#!/bin/bash

if [ -n "$1" ]; then
    data_date=$1
else
  data_date=`date -d '-1 days' +%F`
fi
tmp_vivo_info_clean="
DROP TABLE IF EXISTS vivo_app_warehouse.tmp_vivo_info_clean;
CREATE TABLE IF NOT EXISTS vivo_app_warehouse.tmp_vivo_info_clean
AS
SELECT
    log_id,
    user_id,
    device_id,
    app_id,
    location_id,
    event_time,
    event_type,
    event_detail,
    app_name,
    app_version,
    os_version,
    network_type,
    ip_address,
    province,
    city,
    battery_health,
    memory_usage
FROM vivo_app_warehouse.vivo_user_log
WHERE dt = '${data_date}'
AND user_id IS NOT NULL
AND app_id IS NOT NULL
AND location_id IS NOT NULL;
"
dwd_user_action_log="
WITH t1 AS (
    SELECT
        *
    FROM vivo_app_warehouse.tmp_vivo_info_clean
),t2 AS (
    SELECT
        *
    FROM vivo_app_warehouse.dim_vivo_app_info
    WHERE dt = '${data_date}'
),t3 AS (
    SELECT
        *
    FROM vivo_app_warehouse.vivo_app_info
    WHERE dt = '${data_date}'
),t4 AS (
    SELECT
        *
    FROM vivo_app_warehouse.vivo_region_info
    WHERE dt = '${data_date}'
)
INSERT OVERWRITE TABLE vivo_app_warehouse.dwd_user_action_log PARTITION (dt = '${data_date}')
SELECT
    t1.log_id,
    t1.user_id,
    t2.user_register_date,
    t2.user_first_login_date,
    t2.user_last_login_date,
    t2.user_is_active,
    t2.device_type,
    t3.app_category,
    t3.price,
    t3.avg_rating,
    t3.download_count,
    t1.device_id,
    t1.app_id,
    t1.location_id,
    t1.event_time,
    t1.event_type,
    t1.event_detail,
    t1.app_name,
    t1.app_version,
    t1.os_version,
    t1.network_type,
    t1.ip_address,
    t1.province,
    t1.city,
    t1.battery_health,
    t1.memory_usage
FROM t1
LEFT JOIN t2 ON t1.user_id = t2.user_id
LEFT JOIN t3 ON t1.app_id = t3.app_id
LEFT JOIN t4 ON t1.location_id = t4.location_id
WHERE t2.user_id IS NOT NULL
AND t3.app_id IS NOT NULL
;
"
/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "
${tmp_vivo_info_clean}
${dwd_user_action_log}
"